-- @owner: li-xin12345
-- @date: 2023/7/25
-- @testpoint: ustore普通/分区/二级分区压缩表，其他参数设置可设置的有效值，compresstype从1修改为2，修改成功
-- @history: Modified by li-xin12345 2024/8/13: 研发代码变更，toast表默认btree改成ubtree,增加索引fsm文件，及填充因子变化，导致表大小变化
-- @history: Modified by li-xin12345 2024/9/20: 研发代码变更，填充因子变化，导致表大小变化，优化用例，建表时指定填充因子
-- @history: Modified by li-xin12345 2025.2.12：研发代码变更，取消参数控制，适配用例

--step1:建ustore普通压缩表，其他压缩参数设置可设置的有效值，compresstype=1，并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0099_01 cascade;
create table t_alter_rowcompress_0099_01(columnone integer ,columntwo char(50) ,columnthree varchar(50) ,columnfour integer ,columnfive char(50) ,columnsix varchar(50) ,columnseven char(50) ,columneight char(50) ,columnnine varchar(50) ,columnten varchar(50) ,columneleven char(50) ,columntwelve char(50) ,columnthirteen varchar(50) ,columnfourteen char(50) ,columnfifteem varchar(50)) with (storage_type = ustore, fillfactor = 100, compresstype = 1, compress_chunk_size = 512,  compress_prealloc_chunks = 7, compress_byte_convert = true, compress_diff_convert = true);

insert into t_alter_rowcompress_0099_01 values(generate_series(0, 1000),'test',substring(md5(random()::text), 1, 16), 2, substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16), substring(md5(random()::text), 1, 16));

drop function if exists compress_func_findpath(character varying);
create or replace function compress_func_findpath(character varying)
returns character varying
language plpgsql
as
$body$
declare
relpath character varying;
begin
relpath = (select pg_relation_filepath(relname::regclass) from pg_class where relname = $1);
return relpath;
end;
$body$;
/
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0099_01'));

--step2:修改压缩参数compresstype=2;expect:成功
alter table t_alter_rowcompress_0099_01 set (compresstype = 2);
checkpoint;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0099_01'));

alter table t_alter_rowcompress_0099_01 set (compresstype = 2, compress_chunk_size = 512,  compress_prealloc_chunks = 7, compress_byte_convert = true, compress_diff_convert = true);
checkpoint;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0099_01'));

--step3:删除普通压缩表，建ustore分区压缩表，其他压缩参数设置可设置的有效值，compresstype=1，并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0099_01 cascade;
drop table if exists t_alter_rowcompress_0099_02 cascade;
create table t_alter_rowcompress_0099_02
(prod_id number(6),
 cust_id number,
 time_id date,
 channel_id char(1),
 promo_id number(6),
 quantity_sold number(3),
 amount_sold number(10,2)
)
with (storage_type = ustore, fillfactor = 100, compresstype = 1, compress_chunk_size = 512,  compress_prealloc_chunks = 7, compress_byte_convert = true, compress_diff_convert = true)
partition by range (time_id)
interval('1 day')
( partition p1 values less than ('2023-02-01 00:00:00'),
  partition p2 values less than ('2023-02-02 00:00:00')
);

insert into t_alter_rowcompress_0099_02 values(generate_series(0, 1000), lpad(floor(random() * 1000)::text, 2, '0'), '2023-01-10 00:00:00', 'a', 1, 1, 1);
insert into t_alter_rowcompress_0099_02 values(generate_series(0, 1000), lpad(floor(random() * 1000)::text, 2, '0'), '2023-02-01 00:00:00', 'a', 2, 2, 2);

--step4:修改压缩参数compresstype=2;expect:成功
select relname, reloptions from pg_partition order by relname;
alter table t_alter_rowcompress_0099_02 set (compresstype = 2);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0099_02'));

alter table t_alter_rowcompress_0099_02 set (compresstype = 2, compress_chunk_size = 512,  compress_prealloc_chunks = 7, compress_byte_convert = true, compress_diff_convert = true);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0099_02'));

--step5:删除分区压缩表，建ustore二级分区压缩表，其他压缩参数设置可设置的有效值，compresstype=1，并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0099_02 cascade;
drop table if exists t_alter_rowcompress_0099_03 cascade;
create table t_alter_rowcompress_0099_03
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
)
with (storage_type = ustore, fillfactor = 100, compresstype = 1, compress_chunk_size = 512,  compress_prealloc_chunks = 7, compress_byte_convert = true, compress_diff_convert = true)
partition by list (month_code) subpartition by list (dept_code)
(
  partition p_201901 values ( '201902' )
  (
    subpartition p_201901_a values ( '1' ),
    subpartition p_201901_b values ( '2' )
  ),
  partition p_201902 values ( '201903' )
  (
    subpartition p_201902_a values ( '1' ),
    subpartition p_201902_b values ( '2' )
  )
);

insert into t_alter_rowcompress_0099_03 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0099_03 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0099_03 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0099_03 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0099_03 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0099_03 values('201903', '2', '1', 1);

--step6:修改压缩参数compresstype=2;expect:成功
select relname, reloptions from pg_partition order by relname;
alter table t_alter_rowcompress_0099_03 set (compresstype = 2);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0099_03'));

alter table t_alter_rowcompress_0099_03 set (compresstype = 2, compress_chunk_size = 512,  compress_prealloc_chunks = 7, compress_byte_convert = true, compress_diff_convert = true);
checkpoint;
select relname, reloptions from pg_partition order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0099_03'));

--step7:清理二级分区表;expect:成功
drop function if exists compress_func_findpath(character varying);
drop table if exists t_alter_rowcompress_0099_03 cascade;